package org.hgvc.webapp.ets.spring.dao;

import java.util.Date;
import java.util.List;

import org.hgvc.webapp.ets.bo.Agent;
import org.hgvc.webapp.ets.bo.Agreement;
import org.hgvc.webapp.ets.bo.Guest;
import org.hgvc.webapp.ets.bo.Member;
import org.hgvc.webapp.ets.bo.Prospect;
import org.hgvc.webapp.ets.bo.Voucher;
import org.hgvc.webapp.ets.bo.VoucherEntry;
import org.hgvc.webapp.ets.bo.VoucherEntryDetail;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.jocsb.base.AbstractHibernateDao;
import org.jocsb.base.bo.User;
import org.jocsb.base.util.DateUtil;


public class VoucherHbmDao extends AbstractHibernateDao{
	
	public Guest getGuestByIcNo(String icNo)throws Exception{
		Criteria criteria = getSession().createCriteria(Guest.class);
		criteria.add(Restrictions.eq("icNo", icNo));
		Guest guest=null;
		List<Guest> list=criteria.list();
		if (list.size()>0)
			guest=list.get(0);
		return guest;
	}
	
	public Prospect getProspectByIcNo(String icNo)throws Exception{
		Criteria criteria = getSession().createCriteria(Prospect.class);
		criteria.add(Restrictions.eq("icNo", icNo));
		Prospect prospect=null;
		List<Prospect> list=criteria.list();
		if (list.size()>0)
			prospect=list.get(0);
		return prospect;
	}
	
	public VoucherEntry getVoucherEntryByAgreement(Agreement agreement)throws Exception{
		Criteria criteria = getSession().createCriteria(VoucherEntry.class);
		criteria.add(Restrictions.eq("agreement.agreementId", agreement.getAgreementId()));
		VoucherEntry voucherEntry = null;
		List<VoucherEntry> list=criteria.list();
		if (list.size()>0)
			voucherEntry=list.get(0);
		return voucherEntry;
	}
	
	public VoucherEntry getVoucherEntryByProspect(Member prospect)throws Exception{
		Criteria criteria = getSession().createCriteria(VoucherEntry.class);
		criteria.add(Restrictions.eq("prospect.memberId", prospect.getMemberId()));
		VoucherEntry voucherEntry = null;
		List<VoucherEntry> list=criteria.list();
		if (list.size()>0)
			voucherEntry=list.get(0);
		return voucherEntry;
	}
	
	public List<Voucher> getVoucherList(Voucher startVoucher,Voucher lastVoucher)throws Exception{
		Criteria criteria = getSession().createCriteria(Voucher.class);
		criteria.add(Restrictions.between("voucherNo",startVoucher.getVoucherNo(),lastVoucher.getVoucherNo()));
		criteria.addOrder(Order.asc("voucherNumber"));
		List<Voucher> list=criteria.list();
		return list;
	}
	
	public List<Voucher> getVoucherList(Agent agent, Voucher startVoucher,Voucher lastVoucher)throws Exception{
		Criteria criteria = getSession().createCriteria(Voucher.class);
		criteria.add(Restrictions.between("voucherNo",startVoucher.getVoucherNo(),lastVoucher.getVoucherNo()));
		criteria.add(Restrictions.eq("assignToAgent", agent));
		criteria.addOrder(Order.asc("voucherNumber"));
		List<Voucher> list=criteria.list();
		return list;
	}
	
	public List<Voucher> getVoucherList(User hgvcUser, Voucher startVoucher,Voucher lastVoucher)throws Exception{
		Criteria criteria = getSession().createCriteria(Voucher.class);
		criteria.add(Restrictions.between("voucherNo",startVoucher.getVoucherNo(),lastVoucher.getVoucherNo()));
		criteria.add(Restrictions.eq("assignToHgvcUser", hgvcUser));
		criteria.addOrder(Order.asc("voucherNumber"));
		List<Voucher> list=criteria.list();
		return list;
	}
	
	public List<Voucher> getVoucherUnassignList()throws Exception{
		/*Criteria criteria = getSession().createCriteria(Voucher.class);
		criteria.add(Restrictions.and(Restrictions.isNull("assignToAgent"), Restrictions.isNull("assignToHgvcUser")));
		criteria.addOrder(Order.asc("voucherNumber"));
		//criteria.setMaxResults(50);
		List<Voucher> list=criteria.list();*/
		
		StringBuffer str = new StringBuffer();
		str.append(" select v from Voucher v ");
		str.append(" where v.assignToAgent is null and  assignToHgvcUser is null ");
		str.append(" and v.isDeleted=:isDeleted ");
		str.append(" order by v.voucherNumber asc ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		
		List<Voucher> list=query.list();
		
		return list;
	}
	
	public List<Voucher> getVoucherAssignedListByAgent(Agent agent)throws Exception{
		Criteria criteria = getSession().createCriteria(Voucher.class);
		criteria.add(Restrictions.eq("assignToAgent.agentId", agent.getAgentId()));
		criteria.addOrder(Order.asc("voucherNumber"));
		List<Voucher> list=criteria.list();
		return list;
	}
	
	public List<Voucher> getVoucherAssignedListByUser(User user)throws Exception{
		Criteria criteria = getSession().createCriteria(Voucher.class);
		criteria.add(Restrictions.eq("assignToHgvcUser.userId", user.getUserId()));
		criteria.addOrder(Order.asc("voucherNumber"));
		List<Voucher> list=criteria.list();
		return list;
	}
	
	public List<Voucher> getVoucherList(String voucherNo,Date effectiveDate,Date expiryDate)throws Exception{
		Criteria criteria = getSession().createCriteria(Voucher.class);
		if (voucherNo!=null&&voucherNo.length()>0)
		    criteria.add(Restrictions.eq("voucherNo", voucherNo));
		if (effectiveDate!=null)
		    criteria.add(Restrictions.ge("effectiveDate", DateUtil.formatDateByTime(effectiveDate, 0, 0, 0, 0)));
		if (expiryDate!=null)
		   criteria.add(Restrictions.le("expiryDate", DateUtil.formatDateByTime(expiryDate, 23, 59, 59, 0)));
		criteria.addOrder(Order.asc("voucherNumber"));
		List<Voucher> list=criteria.list();
		return list;
	}
	public VoucherEntry getVoucherEntryByGuest(Guest guest)throws Exception{
		Criteria criteria = getSession().createCriteria(VoucherEntry.class);
		criteria.add(Restrictions.eq("guest.guestId", guest.getGuestId()));
		VoucherEntry voucherEntry = null;
		List<VoucherEntry> list=criteria.list();
		if (list.size()>0)
			voucherEntry=list.get(0);
		return voucherEntry;
	}
	
	public List<VoucherEntryDetail> getVoucherEntryDetailList(VoucherEntry voucherEntry)throws Exception{
		Criteria criteria = getSession().createCriteria(VoucherEntryDetail.class);
		criteria.add(Restrictions.eq("voucherEntry.voucherEntryId", voucherEntry.getVoucherEntryId()));
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		List<VoucherEntryDetail> list=criteria.list();
		return list;
	}
	
	public List<Voucher> getVoucherListByAssignedVoucherEntryUser(User user)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select voucherEntryDetail.voucher from VoucherEntryDetail voucherEntryDetail ");
		str.append(" where voucherEntryDetail.createdBy.userId=:userId");
		str.append(" and voucherEntryDetail.isDeleted=:isDeleted");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("userId", user.getUserId());
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		List<Voucher> list=query.list();
		return list;
	}
	
	public VoucherEntryDetail getVoucherEntryDetailByVoucher(Voucher voucher)throws Exception{
		Criteria criteria=getSession().createCriteria(VoucherEntryDetail.class);
		criteria.add(Restrictions.eq("voucher.voucherId", voucher.getVoucherId()));
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		VoucherEntryDetail voucherEntryDetail=null;
		List<VoucherEntryDetail> list=criteria.list();
		if (list.size()>0)
			voucherEntryDetail=list.get(0);
		return voucherEntryDetail;
	}
	
	public List<VoucherEntryDetail> getVoucherEntryDetailList()throws Exception{
		Criteria criteria=getSession().createCriteria(VoucherEntryDetail.class);
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		Criteria voucherCrt = criteria.createCriteria("voucher");
		voucherCrt.addOrder(Order.asc("voucherNumber"));
		List<VoucherEntryDetail> list=criteria.list();
		return list;
	}
	
	public List<VoucherEntryDetail> getVoucherEntryDetailAvailableList()throws Exception{
		Criteria criteria=getSession().createCriteria(VoucherEntryDetail.class);
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		criteria.add(Restrictions.isNull("complimentaryBookingNo"));
		Criteria voucherCrt = criteria.createCriteria("voucher");
		voucherCrt.addOrder(Order.asc("voucherNumber"));
		List<VoucherEntryDetail> list=criteria.list();
		return list;
	}
	
}
